package cn.wangkai.peanut.db;

import java.math.BigDecimal;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import cn.wangkai.peanut.db.iface.IDbFactory;
import cn.wangkai.peanut.db.iface.IMyDb;
import cn.wangkai.peanut.db.mod.CountTableNum;
import cn.wangkai.peanut.db.mod.GeneratedKey;
import cn.wangkai.peanut.db.util.ListTools;
import cn.wangkai.peanut.db.util.ManagerException;
import cn.wangkai.peanut.db.util.PageModel;
import cn.wangkai.peanut.db.util.SQLUtil;

public class DbFactory implements IDbFactory {

	private static final Log logger = LogFactory.getLog(DbFactory.class);
	/**
	 * 插入数据，得到自动获取的主键
	 * @param sql SQL语句
	 * @param pmdKnownBroken
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public GeneratedKey add(IMyDb db,String sql, Object... params) throws WKDbException, SQLException{
		PreparedStatement stmt = null;
		boolean pmdKnownBroken = false;
		ResultSet rs = null;
		GeneratedKey key = new GeneratedKey();
		try {
			logger.info(ManagerUtil.getSqlMore(sql,params));
			stmt = db.getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			this.fillStatement(stmt, pmdKnownBroken, params);
			key.setUpdateNum(stmt.executeUpdate());
			rs = stmt.getGeneratedKeys();
			if(rs.next())
			key.setGeneratedKey(rs.getObject(1));
		} catch (Exception e) {
			logger.error(e.getMessage(),e.fillInStackTrace());
			throw new WKDbException(e);
		} finally {
			DbUtils.close(rs);
			DbUtils.close(stmt);
		}
	
		return key;
	}


	/**
	 * 更新方法
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException 
	 * @throws WKDbException 
	 * @throws Exception
	 */
	public int execute(IMyDb db,String sql, Object... params) throws SQLException, WKDbException{
		int rows = 0;
		QueryRunner qr = new QueryRunner();
		logger.info(ManagerUtil.getSqlMore(sql,params));
		if(params==null)
			rows = qr.update(db.getConnection(), sql);
		else
			rows = qr.update(db.getConnection(), sql, params);
		return rows;
	}

	/**  
	* 执行批量sql语句  
	* @param sql sql语句  
	* @param params 二维参数数组  
	* @return 受影响的行数的数
	* @throws SQLException 
	 * @throws WKDbException 
	*/  
	public int[] batchexecute(IMyDb db,String sql, Object[]... params) throws SQLException, WKDbException {
		QueryRunner qr = new QueryRunner();
		int[] affectedRows = new int[0];   
		logger.info(ManagerUtil.getSqlMore(sql,params));
		affectedRows = qr.batch(db.getConnection(),sql, params);   
		return affectedRows;   
	}

	public <T> List<T>  query(IMyDb db, Class<?> module,SQLUtil sqlUtil) throws SQLException, WKDbException {
		return query(db, sqlUtil.getSQL(), module, sqlUtil.getParams());
	}
	
	/**
	 * 得到列表数据
	 * @param sql
	 * @param module
	 * @param param
	 * @return
	 * @throws SQLException 
	 * @throws WKDbException 
	 */
	public <T> List<T>  query(IMyDb db,String sql, Class<?> module, List<Object> params) throws SQLException, WKDbException {
		return query(db,sql,module,ListTools.ListtoArray(params));
	}

	
	
	/**
	 * 执行查询方法(使用)
	 * @param <T>
	 * @param sql
	 * @param module
	 * @return
	 * @throws SQLException 
	 * @throws WKDbException 
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public <T> List<T> query(IMyDb db,String sql, Class<?> module, Object... params) throws SQLException, WKDbException {
		List<T> results = null;
		QueryRunner qr = new QueryRunner();
		logger.info(ManagerUtil.getSqlMore(sql,params));
		ResultSetHandler<?> rsh =  new MapListHandler();
		if(module!=null)
			rsh = new BeanListHandler(module);
		if(params==null)
			results = (List<T>) qr.query(db.getConnection(), sql, rsh);
		else
			results = (List<T>) qr.query(db.getConnection(), sql, rsh,params);
		return results;
	}

	public <T> T getOne(IMyDb db, Class<?> module,SQLUtil sqlUtil) throws SQLException, WKDbException {
		if(sqlUtil.getParams()==null){
			return getOne(db,sqlUtil.getSQL(), module);
		}else{
			return getOne(db,sqlUtil.getSQL(), module, sqlUtil.getParams().toArray());
		}
	}
	
	/**
	 * 查询单个数据
	 * @param sql
	 * @param module
	 * @return
	 * @throws SQLException 
	 * @throws WKDbException 
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public <T> T getOne(IMyDb db,String sql, Class<?> module,Object... params) throws SQLException, WKDbException {
		T results = null;
		QueryRunner qr = new QueryRunner();
		logger.info(ManagerUtil.getSqlMore(sql,params));
		ResultSetHandler<?> rsh = null;
		if(module!=null)
			rsh = new BeanHandler(module);
		else
			rsh =  new MapHandler();
		if(params==null)
			results = (T) qr.query(db.getConnection(), sql, rsh);
		else
			results = (T) qr.query(db.getConnection(), sql, rsh,params);
		return results;
	}
	
	public BigDecimal getcount(IMyDb db,SQLUtil sqlUtil) throws SQLException, WKDbException{
		return getcount(db,sqlUtil.getSQL(),sqlUtil.getParams());
	}
	/**
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException 
	 * @throws WKDbException 
	 */
	public BigDecimal getcount(IMyDb db,String sql,List<Object> params) throws SQLException, WKDbException{
		return getcount(db,sql,ListTools.ListtoArray(params));
	}
	/**
	 * 
	 * @param sql
	 * @param param
	 * @return
	 * @throws SQLException 
	 * @throws WKDbException 
	 */
	public BigDecimal getcount(IMyDb db,String sql, Object... param) throws SQLException, WKDbException{
		CountTableNum countTableNum = (CountTableNum) getOne(db,sql, CountTableNum.class,param);
		return new BigDecimal(countTableNum.getTj());
	}
	
	/**
	 * 
	 * @param db.getConnection()
	 * @param module
	 * @param selects
	 * @param sqlUtil
	 * @param pagesize
	 * @param pagenum
	 * @param Orders
	 * @param Descs
	 * @return
	 * @throws SQLException
	 * @throws WKDbException 
	 */
	public <T> PageModel<T> get_cutpage(IMyDb db,Class<?> module,String selects,SQLUtil sqlUtil,int pagesize,int pagenum,String[] Orders,Boolean[] Descs) throws SQLException, WKDbException{
		return get_cutpage(db, module, selects, sqlUtil.getSQL(), pagesize, pagenum, sqlUtil.getParams(), Orders, Descs);
	}

	/**
	 * 
	 * @param db.getConnection()
	 * @param module
	 * @param selects
	 * @param sqlUtil
	 * @param pagesize
	 * @param pagenum
	 * @param Orders
	 * @param Descs
	 * @return
	 * @throws SQLException
	 * @throws WKDbException 
	 */
	public <T> PageModel<T> get_cutpage(IMyDb db,Class<?> module,String selects,SQLUtil sqlUtil,int pagesize,int pagenum,String Orders,Boolean Descs) throws SQLException, WKDbException{
		return get_cutpage(db, module, selects, sqlUtil.getSQL(), pagesize, pagenum, sqlUtil.getParams(), Orders, Descs);
	}
	
	/**
	 * 
	 *	一、MySQL
	 *		driverClass：com.mysql.jdbc.Driver
	 *		jdbcUrl：jdbc:mysql://192.168.0.1:3306/yourDBName 硬编码（不建议）
     *		Class.forName("com.mysql.jdbc.Driver", true, Thread.currentThread().getContextClassLoader());
     *		db.getConnection()=DriverManager.getConnection("jdbc:mysql://192.168.0.1:3306/yourDBName?user=userName&password=123");
	 *	二、Oracle
	 *		driverClass：oracle.jdbc.driver.OracleDriver
	 *		jdbcUrl：jdbc:oracle:thin:@192.168.0.1:1521:yourDBName
	 *	三、SQL Server
	 *		driverClass：com.microsoft.sqlserver.jdbc.SQLServerDriver 005版本及以后）
	 *					com.microsoft.jdbc.sqlserver.SQLServerDriver 000版本
	 *		jdbcUrl：jdbc:sqlserver://192.168.0.1:1433;databasename=yourDBName
	 *	四、Sybase
	 *		driverClass
	 *		jdbcUrl??
	 *	五、DB2
	 *		1、db2java.zip
	 *			driverClass：COM.ibm.db2.jdbc.net.DB2Driver
	 *			jdbcUrl：jdbc:db2://ip:6789/DBNAME
	 *		2、db2jcc.jar
	 *			driverClass：com.ibm.db2.jcc.DB2Driver
	 *			jdbcUrl：jdbc:db2://ip:port/DBNAME
	 *	 六、Informix
	 *		driverClass
	 *		jdbcUrl
	 *	七、PostgreSQL
	 *		driverClass
	 *		jdbcUrl
	 * @param <T>
	 * @param module
	 * @param selects
	 * @param sql
	 * @param pagesize
	 * @param pagenum
	 * @param params
	 * @param Orders
	 * @param Descs
	 * @return
	 * @throws SQLException 
	 * @throws WKDbException 
	 */
	public <T> PageModel<T> get_cutpage(IMyDb db,Class<?> module,String selects,String sql,int pagesize,int pagenum,List<Object> params,List<String> Orders,List<Boolean> Descs) throws SQLException, WKDbException{
		String[] Orders2=null;
		Boolean[] Descs2=null;
		if(Orders!=null){
			int i=0;
			Orders2 = new String[Orders.size()];
			for (String order : Orders) {
				Orders2[i]=order;
				i++;
			}
		}
		if(Descs!=null){
			int i=0;
			Descs2 = new Boolean[Descs.size()];
			for (Boolean des : Descs) {
				Descs2[i]=des;
				i++;
			}
		}
		return get_cutpage(db, module, selects, sql, pagesize, pagenum, params, Orders2, Descs2);
	}

	/**
	 * 
	 * @param db.getConnection()
	 * @param module
	 * @param selects
	 * @param sql
	 * @param pagesize
	 * @param pagenum
	 * @param params
	 * @param Orders
	 * @param Descs
	 * @return
	 * @throws SQLException
	 * @throws WKDbException 
	 */
	public <T> PageModel<T> get_cutpage(IMyDb db,Class<?> module,String selects,String sql,int pagesize,int pagenum,List<Object> params,String Orders,Boolean Descs) throws SQLException, WKDbException{
		String[] Orders2=null;
		Boolean[] Descs2=null;
		if(Orders!=null){
			Orders2 = new String[]{Orders};
		}
		if(Descs!=null){
			Descs2 = new Boolean[]{Descs};
		}
		return get_cutpage(db, module, selects, sql, pagesize, pagenum, params, Orders2, Descs2);
	}

	/**
	 * 
	 * @param db.getConnection()
	 * @param module
	 * @param selects
	 * @param sql
	 * @param pagesize
	 * @param pagenum
	 * @param params
	 * @param Orders
	 * @param Descs
	 * @return
	 * @throws SQLException
	 * @throws WKDbException 
	 */
	public <T> PageModel<T> get_cutpage(IMyDb db,Class<?> module,String selects,String sql,int pagesize,int pagenum,List<Object> params,String[] Orders,Boolean[] Descs) throws SQLException, WKDbException{
		PageModel<T> lists = new PageModel<T>();
		lists.setRowcount(getcount(db,"SELECT count(*) as tj "+sql, params).longValue());
		lists.setPagerow(pagesize);
		lists.setPagenum(pagenum);
		if(StringUtils.isBlank(selects)){
			selects =" * ";
		}
		int DbType = db.getDb().getDbtype();
		logger.debug("Dbtype="+DbType);
		List<T> t = null;
		if(lists.getRowcount()>0){
			sql = ManagerUtil.getQuery(lists.getRowcount().intValue(), pagesize, pagenum, selects+sql, Orders,Descs,DbType,lists.getRowcount());
			logger.info(ManagerUtil.getSqlMore(sql,params==null?null:params.toArray()));
			t =query(db,sql, module, params);
		}else{
			t = new ArrayList<T>();
		}
		lists.setLists(t);
		return lists;
	}
	/*******************************************以下对象相关****************************************/
	/**
	 * 
	 * @param obj
	 * @param keyword
	 * @param keywordauto
	 * @return
	 * @throws ManagerException 
	 * @throws WKDbException 
	 * @throws Exception
	 */
	public int add(IMyDb db,Object obj,boolean keywordauto,String... keyword) throws SQLException, ManagerException, WKDbException {
		String sql = ManagerUtil.InsertSQL(obj.getClass(), keyword, keywordauto);
		return execute(db,sql, ManagerUtil.getInsertParames(obj,sql));
	}
	
	/**
	 * 
	 * @param db.getConnection()
	 * @param obj
	 * @return
	 * @throws ManagerException 
	 * @throws WKDbException 
	 * @throws SQLException 
	 * @throws Exception
	 */
	public int addselect(IMyDb db,Object obj) throws SQLException, WKDbException, ManagerException {
		return execute(db,ManagerUtil.InsertSQL(obj), ManagerUtil.getInsertParames(obj,ManagerUtil.InsertSQL(obj)));
	}
	
	/**
	 * 
	 * @param db.getConnection()
	 * @param obj
	 * @return
	 * @throws ManagerException
	 * @throws WKDbException
	 * @throws SQLException
	 */
	public GeneratedKey addselectgetID(IMyDb db,Object obj) throws ManagerException, WKDbException, SQLException {
		return add(db,ManagerUtil.InsertSQL(obj), ManagerUtil.getInsertParames(obj,ManagerUtil.InsertSQL(obj)));
	}
	
	/**
	 * 
	 * @param db.getConnection()
	 * @param obj
	 * @return
	 * @throws ManagerException
	 * @throws WKDbException
	 * @throws SQLException
	 */
	public GeneratedKey add(IMyDb db,Object obj) throws ManagerException, WKDbException, SQLException {
		return add(db,ManagerUtil.InsertSQL(obj),ManagerUtil.getInsertParames(obj,ManagerUtil.InsertSQL(obj)));
	}
	
	/**
	 * 
	 * @param db.getConnection()
	 * @param obj
	 * @return
	 * @throws WKDbException
	 * @throws SQLException
	 * @throws ManagerException
	 */
	public Long addgetLongID(IMyDb db,Object obj) throws WKDbException, SQLException, ManagerException{
		return addselectgetID(db,obj).getLongKey();
	}

	/**
	 * 
	 * @param db.getConnection()
	 * @param obj
	 * @return
	 * @throws WKDbException
	 * @throws SQLException
	 * @throws ManagerException
	 */
	public Integer addgetIntegerID(IMyDb db,Object obj) throws WKDbException, SQLException, ManagerException{
		return addselectgetID(db,obj).getIntegerKey();
	}
	
	/**
	 * 
	 * @param db.getConnection()
	 * @param obj
	 * @return
	 * @throws WKDbException
	 * @throws SQLException
	 * @throws ManagerException
	 */
	public BigDecimal addgetBigDecimalID(IMyDb db,Object obj) throws WKDbException, SQLException, ManagerException{
		return addselectgetID(db,obj).getBigDecimalKey();
	}

	/**
	 * 
	 * @param obj
	 * @param keyword
	 * @return
	 * @throws ManagerException 
	 * @throws WKDbException 
	 * @throws SQLException 
	 * @throws Exception
	 */
	public int update(IMyDb db,Object obj,String... keywords) throws ManagerException, WKDbException, SQLException {
		return ManagerUtil.update(db.getConnection(), obj, ManagerUtil.UpdateSQL(obj.getClass(), keywords));
	}
	
	/**
	 * 
	 * @param db.getConnection()
	 * @param obj
	 * @param keywords
	 * @return
	 * @throws ManagerException
	 * @throws SQLException
	 * @throws WKDbException 
	 */
	public int delobj(IMyDb db,Object obj,String... keywords) throws ManagerException, SQLException, WKDbException {
		return ManagerUtil.delete(db.getConnection(), obj, ManagerUtil.DeleteSQL(obj.getClass(), keywords));
	}
	
	/**
	 * 
	 * @param db.getConnection()
	 * @param obj
	 * @param sql
	 * @return
	 * @throws ManagerException
	 * @throws SQLException
	 * @throws WKDbException 
	 */
	public int del(IMyDb db,Object obj,String sql) throws ManagerException, SQLException, WKDbException  {
		return ManagerUtil.delete(db.getConnection(), obj, sql);
	}

	/**
	 * 
	 * @param stmt
	 * @param pmdKnownBroken
	 * @param params
	 * @throws SQLException
	 */
	private void fillStatement(PreparedStatement stmt, boolean pmdKnownBroken,Object[] params) throws SQLException {
		if (params == null) return;
		ParameterMetaData pmd = null;
		if (!pmdKnownBroken) {
			pmd = stmt.getParameterMetaData();
			if (pmd.getParameterCount() < params.length) {
				throw new SQLException("Too many parameters: expected "
						+ pmd.getParameterCount() + ", was given "
						+ params.length);
			}
		}
		for (int i = 0; i < params.length; i++) {
			logger.debug(params[i]);
			if (params[i] != null) {
				stmt.setObject(i + 1, params[i]);
			} else {
				// VARCHAR works with many drivers regardless of the actual column type. Oddly, NULL and OTHER don't work with Oracle's drivers.
				int sqlType = Types.VARCHAR;
				if (!pmdKnownBroken) {
					try {
						sqlType = pmd.getParameterType(i + 1);
					} catch (SQLException e) {
						pmdKnownBroken = true;
					}
				}
				stmt.setNull(i + 1, sqlType);
			}
		}
	}
}
